/*
 * Copyright 2011 yura.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.opu.db_vdumper.util.datacopy;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import static java.sql.Types.*;

/**
 * The Database class is used to provide all of the low-level
 * JDBC services for the Datamover. Database specific
 * implementations should be handled in derived classes,
 * for example the MySQL class.
 *
 * @author yura
 */
public class Database {
    /*
     * The database connection
     */

    protected Connection connection;

    public Database() {
    }

    public Database(Connection connection) {
        this.connection = connection;
    }

    public Database(String driver, String url, String user, String pswd) 
    		throws DatabaseException {
        connect(driver, url, user, pswd);
    }

    

    /**
     * Open a connection to the database.
     *
     * @param driver The database driver to use.
     * @param url The datbase connection URL to use.
     * @throws DatabaseException Thrown if an error occurs while connecting.
     */
    public void connect(String driver, String url, String user, String pswd) 
    		throws DatabaseException {
        try {
            Class.forName(driver).newInstance();
            connection = DriverManager.getConnection(url, user, pswd);
        } catch (Exception e) {
            throw new DatabaseException(e);
        }
    }

    public void setConnection(Connection connection) {
        this.connection = connection;
    }

    public Connection getConnection() {
        return connection;
    }

    /**
     * Called to close the database.
     *
     * @throws DatabaseException Thrown if the connection cannot be closed.
     */
    public void close()
            throws DatabaseException {
        try {
            connection.close();
        } catch (SQLException e) {
            throw (new DatabaseException(e));
        }
    }

    /**
     * Check to see if the specified type is numeric.
     *
     * @param type The type to check.
     * @return Returns true if the type is numeric.
     */
    public boolean isNumeric(int type) {
        if (type == BIGINT || type == DECIMAL || type == DOUBLE || type == FLOAT
                || type == INTEGER || type == NUMERIC || type == SMALLINT
                || type == TINYINT || type == BIT || type == REAL || type == ROWID) {
            return true;
        } else {
            return false;
        }

    }

    /**
     * Generate the DROP statement for a table.
     *
     * @param table The name of the table to drop.
     * @return The SQL to drop a table.
     */
    public String generateDrop(String table, String schema) {
        StringBuilder result = new StringBuilder();
        result.append("DROP TABLE ");
        result.append(DataMoverUtility.tableStr(table, schema));
        result.append(";");
        return result.toString();
    }

    /**
     * Generate the create statement to create the specified table.
     *
     * @param table The table to generate a create statement for.
     * @return The create table statement.
     * @throws DatabaseException If a database error occurs.
     */
    public String generateCreate(String table, String schema)
            throws DatabaseException {

        StringBuilder result = new StringBuilder();

        try {
            StringBuilder sql = new StringBuilder();

            sql.append("SELECT * FROM ");
            sql.append(DataMoverUtility.tableStr(table, schema));
            ResultSet rs = executeQuery(sql.toString());
            ResultSetMetaData md = rs.getMetaData();

            result.append("CREATE TABLE ");
            result.append(DataMoverUtility.tableStr(table, schema));
            result.append(" ( ");

            for (int i = 1; i <= md.getColumnCount(); i++) {
                if (i != 1) {
                    result.append(", ");
                }
                result.append('\"');
                result.append(md.getColumnName(i));
                result.append("\" ");

                int precision = md.getPrecision(i);
                String stype = DataMoverUtility.processType(md.getColumnTypeName(i),
                        precision, connection);
                int ctype = md.getColumnType(i);

                if (ctype == BLOB || ctype == CLOB) {
                    throw new DatabaseException("Can't worck with clob and blob data");
                }

                if (ctype == ARRAY){
                    int count = 0;
                    for (int j = 0; j < stype.length(); j++) {
                        if (stype.charAt(j) == '_'){
                            count ++;
                        } else {
                            break;
                        }
                    }
                    stype = stype.substring(count);
                    for (int j = 0; j < count; j++) {
                        stype += "[]";
                    }
                }
                result.append(stype);
                
                if (precision < 65535 && !isNumeric(ctype) && ctype != DATE && ctype != ARRAY
                        //&& !"oid".equals(stype)
                        ) {
                    result.append('(');
                    result.append(precision);
//                    if (md.getScale(i) > 0) {
//                        result.append(',');
//                        result.append(md.getScale(i));
//                    }
                    result.append(") ");
                } else {
                    result.append(' ');
                }

//                if (this.isNumeric(md.getColumnType(i))) {
//                    if (!md.isSigned(i)) {
//                        result.append("UNSIGNED ");
//                    }
//                }

                if (md.isNullable(i) == ResultSetMetaData.columnNoNulls) {
                    result.append("NOT NULL ");
                }
//                else {
//                    result.append("NULL ");
//                }
                if (md.isAutoIncrement(i)) {
//                    result.append(" auto_increment");
                }
            }

            DatabaseMetaData dbm = connection.getMetaData();
            ResultSet primary = dbm.getPrimaryKeys(null, schema, table);
            boolean first = true;
            while (primary.next()) {
                if (first) {
                    first = false;
                    result.append(',');
                    result.append("PRIMARY KEY(");
                } else {
                    result.append(",");
                }

                result.append(primary.getString("COLUMN_NAME"));
            }

            if (!first) {
                result.append(')');
            }

            result.append(" ); ");
        } catch (SQLException e) {
            throw (new DatabaseException(e));
        }

        return result.toString();
    }

    /**
     * Execute a SQL query and return a ResultSet.
     *
     * @param sql The SQL query to execute.
     * @return The ResultSet generated by the query.
     * @throws DatabaseException If a datbase error occurs.
     */
    public ResultSet executeQuery(String sql) throws DatabaseException {
        Statement stmt = null;

        try {
            stmt = connection.createStatement();
            return stmt.executeQuery(sql);
        } catch (SQLException e) {
            throw (new DatabaseException(e.getMessage() + " (" + sql + ")", e));
        }

    }

    /**
     * Execute a INSERT, DELETE, UPDATE, or other statement that
     * does not return a ResultSet.
     *
     * @param sql The query to execute.
     * @throws DatabaseException If a database error occurs.
     */
    public DbResponse execute(String sql) throws DatabaseException {
        Statement stmt = null;
        try {
            stmt = connection.createStatement();
            boolean execute = stmt.execute(sql);
            return new DbResponse(true, stmt.getWarnings());
        } catch (SQLException e) {
            throw (new DatabaseException(e.getMessage() + " (" + sql + ")", e));
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    /**
     * Get a list of all tables in the database.
     *
     * @return A list of all tables in the database.
     * @throws DatabaseException If a database error occurs.
     */
    public Collection<String> listTables() throws DatabaseException {
        Collection<String> result = new ArrayList<String>();
        ResultSet rs = null;

        try {
            DatabaseMetaData dbm = connection.getMetaData();

            String types[] = {"TABLE"};
            rs = dbm.getTables(null, null, "", types);

            while (rs.next()) {
                String str = rs.getString("TABLE_NAME");
                result.add(str);
            }
        } catch (SQLException e) {
            throw (new DatabaseException(e));
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                }
            }
        }

        return result;
    }

    /**
     * Determine if a table exists.
     *
     * @param table The name of the table.
     * @return True if the table exists.
     * @throws DatabaseException A database error occured.
     */
    public boolean tableExists(String table, String schema) throws DatabaseException {
        boolean result = false;
        ResultSet rs = null;

        try {
            DatabaseMetaData dbm = connection.getMetaData();

            String types[] = {"TABLE"};
            rs = dbm.getTables(null, schema, table, types);
            result = rs.next();
            rs.close();
        } catch (SQLException e) {
            throw (new DatabaseException(e));
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                }
            }
        }
        return result;
    }

    /**
     * Get a list of all of the columns on a table.
     *
     * @param table The table to check.
     * @return A list of all of the columns.
     * @throws DatabaseException If a database error occurs.
     */
    public Collection<String> listColumns(String table, String schema) 
            throws DatabaseException {
        
        Collection<String> result = new ArrayList<String>();
        ResultSet rs = null;

        try {

            DatabaseMetaData dbm = connection.getMetaData();
            rs = dbm.getColumns(null, schema, table, null);
            while (rs.next()) {
                result.add(rs.getString("COLUMN_NAME"));
            }
        } catch (SQLException e) {
            throw new DatabaseException(e);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                }
            }
        }
        return result;
    }

    /**
     * Create a prepared statement.
     *
     * @param sql The SQL of the prepared statement.
     * @return The PreparedStatement that was created.
     * @throws DatabaseException If a database error occurs.
     */
    public MiniPreparedStatement prepareStatement(String sql)
            throws DatabaseException {

        PreparedStatement statement = null;

        try {
            statement = connection.prepareStatement(sql);
        } catch (SQLException e) {
            throw (new DatabaseException(e));
        }

        return new MiniPreparedStatementImpl(statement);
    }
}
